﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class TopicDetailsDAL
    {
        private const string Waming = "SqlServerDAL.TopicDetailsDAL";

        public bool Save(TopicDetailsDto dto)
        {
            try
            {
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@TdTopicId", SqlDbType.Int) {Value = dto.TdTopicId},
                                         new SqlParameter("@TdContent", SqlDbType.VarChar) {Value = dto.TdContent},
                                         new SqlParameter("@TdTime", SqlDbType.DateTime) {Value = dto.TdTime},
                                         new SqlParameter("@TdUserId", SqlDbType.Int) {Value = dto.TdUserId},
                                         new SqlParameter("@TdParentId", SqlDbType.Int) {Value = dto.TdParentId}
                                     };
                if (dto.TdTopicId == 0)
                {
                    sql =
                        "insert into TopicDetails(TdTopicId,TdContent,TdTime,TdUserId,TdParentId) values(@TdTopicId,@TdContent,@TdTime,@TdUserId,@TdParentId)";
                }
                else
                {
                    sql =
                        "update TopicDetails set TdTopicId=@TdTopicId,TdContent=@TdContent,TdTime=@TdTime,TdUserId=@TdUserId,TdParentId=@TdParentId where TdId=@TdId";
                    parameters.Add(new SqlParameter("@TdId", SqlDbType.Int) { Value = dto.TdId });
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(TopicDetailsDto dto)", ex);
                return false;
            }
        }

        public Hashtable FindOne(int topicId)
        {
            try
            {
                Hashtable ht = null;
                const string sql = "select top 1 * from TopicDetails";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (topicId != 0)
                {
                    whereSql += " where TdTopicId=@TdTopicId";
                    parameters.Add(new SqlParameter("@TdTopicId", SqlDbType.Int) {Value = topicId});
                }
                using (
                    var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                      parameters.ToArray()))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch(Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindOne(int topicId)",ex);
                return null;
            }
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, int topicId, int paranId, string order)
        {
            try
            {
                var hts = new List<Hashtable>();
                order = string.IsNullOrEmpty(order) ? "order by TdId desc" : order;
                var sql = @"select row_number() over (" + order + ") as rowNumber,* from TopicDetails ";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (topicId != 0)
                {
                    whereSql += " where TdTopicId=@TdTopicId";
                    parameters.Add(new SqlParameter("@TdTopicId", SqlDbType.Int) { Value = topicId });
                }
                if (paranId > -1)
                {
                    whereSql += whereSql == "" ? " where TdParentId=@TdParentId" : " and TdParentId=@TdParentId";
                    parameters.Add(new SqlParameter("@TdParentId", SqlDbType.Int) { Value = paranId });
                }
                sql = string.Format(
                    "select * from ({0} {1}) as TopicDetails where rowNumber between {2} AND {3}",
                    sql, whereSql, firstResult, maxResult);
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SlicedFindAll(int firstResult, int maxResult, int topicId, string order)", ex);
                return null;
            }
        }

        public int SelectCount(int topicId, int paranId)
        {
            try
            {
                const string sql = "select count(*) from TopicDetails";
                var parameters = new List<SqlParameter>();
                var whereSql = "";
                if (topicId != 0)
                {
                    whereSql += " where TdTopicId=@TdTopicId";
                    parameters.Add(new SqlParameter("@TdTopicId", SqlDbType.Int) { Value = topicId });
                }
                if (paranId > -1)
                {
                    whereSql += whereSql == "" ? " where TdParentId=@TdParentId" : " and TdParentId=@TdParentId";
                    parameters.Add(new SqlParameter("@TdParentId", SqlDbType.Int) { Value = paranId });
                }
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                  parameters.ToArray());
                return Convert.ToInt32(red);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectCount(int topicId)", ex);
                return -1;
            }
        }
    }
}
